﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
    public class peopledal
    {
        SqlConnection conn = new SqlConnection("server= LAPTOP-KE3OLTQM;database=RenLiZiYuanGuanli;uid=sa;pwd=123456");
        public List<Models.Usertab_Role> list()//人事变动显示
        {
            using (conn)
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("prlist", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter dsa = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                dsa.Fill(dt);
                List<Models.Usertab_Role> list = new List<Models.Usertab_Role>();
                for (var i = 0; i < dt.Rows.Count; i++)
                {
                    list.Add(new Models.Usertab_Role()
                    {
                        RoleId = Convert.ToInt32(dt.Rows[i]["RoleId"]),
                        userName = Convert.ToString(dt.Rows[i]["userName"]),
                        userRealname = Convert.ToString(dt.Rows[i]["userRealname"]),
                        RoleName = Convert.ToString(dt.Rows[i]["RoleName"])
                    });
                }
                return list;
            }
        }
        /// <summary>
        /// 人事变动修改
        /// </summary>
        /// <returns></returns>
        /// 

        public List<Models.Staff> tuixiu()//退休人员管理
        {
            using (conn)
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("prtuixi", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter dsa = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                dsa.Fill(dt);
                List<Models.Staff> list = new List<Models.Staff>();
                for (var i = 0; i < dt.Rows.Count; i++)
                {
                    list.Add(new Models.Staff()
                    {
                        Staff_ID = Convert.ToInt32(dt.Rows[i]["Staff_ID"]),
                        Staff_Name = Convert.ToString(dt.Rows[i]["Staff_Name"]),
                        Staff_Sex = Convert.ToInt32(dt.Rows[i]["Staff_Sex"]),
                        Staff_Age = Convert.ToInt32(dt.Rows[i]["Staff_Age"]),
                        Staff_Money = Convert.ToDecimal(dt.Rows[i]["Staff_Money"]),
                        Staff_Address = Convert.ToString(dt.Rows[i]["Staff_Address"]),
                        Staff_GL = Convert.ToInt32(dt.Rows[i]["Staff_GL"]),
                        Hiredate = Convert.ToString(dt.Rows[i]["Hiredate"]),
                        BuMen = Convert.ToString(dt.Rows[i]["BuMen"]),
                        JishuTechang = Convert.ToString(dt.Rows[i]["JishuTechang"]),
                        Image_Url = Convert.ToString(dt.Rows[i]["Image_Url"]),
                        ttime = Convert.ToString(dt.Rows[i]["ttime"]),
                        tcontent = Convert.ToString(dt.Rows[i]["tcontent"])
                    });
                }
                return list;
            }
        }
        public List<Models.Staff> xin()//新进人员管理
        {
            using (conn)
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter dsa = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                dsa.Fill(dt);
                List<Models.Staff> list = new List<Models.Staff>();
                for (var i = 0; i < dt.Rows.Count; i++)
                {
                    list.Add(new Models.Staff()
                    {
                        Staff_ID = Convert.ToInt32(dt.Rows[i]["Staff_ID"]),
                        Staff_Name = Convert.ToString(dt.Rows[i]["Staff_Name"]),
                        Staff_Sex = Convert.ToInt32(dt.Rows[i]["Staff_Sex"]),
                        Staff_Age = Convert.ToInt32(dt.Rows[i]["Staff_Age"]),
                        Staff_Money = Convert.ToDecimal(dt.Rows[i]["Staff_Money"]),
                        Staff_Address = Convert.ToString(dt.Rows[i]["Staff_Address"]),
                        Staff_GL = Convert.ToInt32(dt.Rows[i]["Staff_GL"]),
                        Train = Convert.ToInt32(dt.Rows[i]["Train"]),
                        Hiredate = Convert.ToString(dt.Rows[i]["Hiredate"]),
                        BuMen = Convert.ToString(dt.Rows[i]["BuMen"]),
                        RenyuanZT = Convert.ToInt32(dt.Rows[i]["RenyuanZT"]),
                        JishuTechang = Convert.ToString(dt.Rows[i]["JishuTechang"]),
                        Image_Url = Convert.ToString(dt.Rows[i]["Image_Url"]),
                    });
                }
                return list;
            }
        }
        /// <summary>
        /// 查询人员信息
        /// </summary>
        /// <param name="Staff_Name"></param>
        /// <returns></returns>
        public List<Models.Staff> charen(string Staff_Name)
        {

            using (conn)
            {
                string sql = string.Format("select * from Staff where Staff_Name like '%{0}%' ", Staff_Name);
                SqlCommand comd = new SqlCommand(sql, conn);
                SqlDataAdapter dap = new SqlDataAdapter(comd);
                DataTable tb = new DataTable();
                List<Models.Staff> list = new List<Models.Staff>();
                dap.Fill(tb);
                foreach (DataRow item in tb.Rows)
                {
                    Models.Staff w = new Models.Staff();
                    w.Staff_ID = Convert.ToInt32(item["Staff_ID"]);
                    w.Staff_Name = item["Staff_Name"].ToString();
                    w.Staff_Sex = Convert.ToInt32(item["Staff_Sex"]);
                    w.Staff_Age = Convert.ToInt32(item["Staff_Age"]);
                    w.Staff_Money = Convert.ToDecimal(item["Staff_Money"]);
                    w.Staff_Address = item["Staff_Address"].ToString();
                    w.Staff_GL = Convert.ToInt32(item["Staff_GL"]);
                    w.Train = Convert.ToInt32(item["Train"]);
                    w.Hiredate = item["Hiredate"].ToString();
                    w.BuMen = item["BuMen"].ToString();
                    w.RenyuanZT = Convert.ToInt32(item["RenyuanZT"]);
                    w.JishuTechang = item["JishuTechang"].ToString();
                    w.Image_Url = item["Image_Url"].ToString();
                    list.Add(w);
                }
                try
                {
                    return list;
                }
                catch (Exception)
                {

                    throw;
                }

            }
        }
    }
}

